package idv.arthur.work;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.google.gson.Gson;

/**
 * Servlet implementation class QueryUser
 */
//@WebServlet(description = "查詢使用者以進行修改或刪除", urlPatterns = { "/QueryUser" })
public class QueryUser extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public QueryUser() {
        super();
    }

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		request.setCharacterEncoding("UTF-8");
		String q_userID = request.getParameter("q_userID");
		String q_cfromDate = request.getParameter("q_cfromDate");
		String q_ctoDate = request.getParameter("q_ctoDate");
		String[] group = request.getParameter("groupList").split(",");
		String strSQL = "SELECT t1.userID, t1.nickName FROM tms_personnel t1 WHERE t1.userID=t1.userID AND ";		

		if (group.length > 0 && !"".equalsIgnoreCase(group[0].trim()) ) {
			strSQL = "SELECT t1.userID, t1.nickName FROM tms_personnel t1,tms_roleusermapping t2 WHERE t1.userID = t2.userID AND ";
			for (int i=0;i<group.length;i++) {
				strSQL += "t2.roleID='" + group[i] + "' AND ";
			}
		}
		
		if (q_userID != null && !q_userID.trim().equals("")) {
			strSQL += "t1.userID='" + q_userID + "' AND ";
		} 
		
		if ((q_cfromDate != null && !q_cfromDate.trim().equals(""))&&(q_ctoDate != null && !q_ctoDate.trim().equals(""))) {			//兩個都有輸入
			strSQL += "t1.createdTime BETWEEN '" + q_cfromDate + "' AND '" + q_ctoDate +"' AND ";
		} else if ((q_cfromDate != null && !q_cfromDate.trim().equals(""))&&!(q_ctoDate != null && !q_ctoDate.trim().equals(""))) {	//僅有起
			strSQL += "t1.createdTime BETWEEN '" + q_cfromDate + "' AND '" + "2020-12-31' AND ";
		} else if (!(q_cfromDate != null && !q_cfromDate.trim().equals(""))&&(q_ctoDate != null && !q_ctoDate.trim().equals(""))) {	//僅有迄
			strSQL += "t1.createdTime BETWEEN '" + "1999-12-31" + "' AND '" + q_ctoDate +"' AND ";
		}
		
		strSQL = strSQL.substring(0, strSQL.length()-4);		//將尾巴的 AND 拿掉
		
		Connection conn = null;
		Widgets wid = new Widgets();
		PreparedStatement ps = null;
		ResultSet rs = null;
		ArrayList<User> users = new ArrayList<User>();
		String tt = "";
		
		try {
			conn = wid.getConn(true);
			ps = conn.prepareStatement(strSQL);
			rs = ps.executeQuery();
			while (rs.next()) {
				User user = new User();
				ArrayList<Role> roles = new ArrayList<Role>();
				user.setUserID(rs.getString("userID"));
				user.setNickName(rs.getString("nickName"));
				user.setRoles(roles);
				users.add(user);
			}
			if (users.size() > 0) {
				tt = new Gson().toJson(users);
				tt = tt.substring(1, tt.length());
				tt = "{\"sqlData\":["+tt+",\"strSQL\":\""+strSQL+"\"}";
			} else {
				tt = new Gson().toJson(users);
				tt = tt.replace("[", "{\"sqlData\":[")+ ",\"strSQL\":\""+strSQL+"\"}";
			}
		} catch (SQLException e) {
			tt = "{\"errorMsg\":\""+wid.keepErrorMes(conn, ps, e, "", true)+"\"}";
			tt = tt.replace("[", "{\"strSQL\":[")+ ",\"strSQL\":\""+strSQL+"\"}";
		}  finally {
			try {
				if (rs!= null) {rs.close();}
				if (ps!= null) {ps.close();}
				if (conn!= null) {conn.close();}
			} catch (SQLException e) {
				tt = "{\"errorMsg\":\""+e.toString()+"\"}";
			}
		}

		response.setContentType("application/json");
		response.setCharacterEncoding("UTF-8");
		response.getWriter().write(tt);
	}
}
